package com.o2o.cleaning.month.platform.ebusiness_plat.jumei_2019_7.jumei_lyc

import com.alibaba.fastjson.JSON
import org.apache.spark.sql.SparkSession

object jumei_count {

  def main(args:Array[String]): Unit ={
    val spark = SparkSession.builder().appName("jumei_count").master("local[*]")
      .config("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
      .config("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
      .config("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
      .getOrCreate()
    val sc = spark.sparkContext
    val sqlContext = spark.sqlContext

    sqlContext.read.json("s3a://o2o-sourcedata/obs-source-2019/7/Jumei/jumei_app_sellCount/")
      .select("good_id","add_to_field","is_onsell")
      .registerTempTable("SourceData")

   val aa =  sqlContext.read.json("s3a://o2o-dataproces-group/liu_taowei/month_data/plat_need/jumei/is_onsell_false/*")
    aa.registerTempTable("lastData")

//    关联不到的下架的相乘
    sqlContext.sql(
      """
        |select
        |count(good_id) count_id,
        |sum(sellCount) sellCount,
        |sum(sellCount * priceText) salesAmount
        |from(
        |     select
        |     a.good_id,
        |     add_to_field[size(add_to_field)-1].sellCount sellCount,
        |     add_to_field[size(add_to_field)-1].priceText priceText
        |     from SourceData a
        |     left join
        |     lastData b
        |     on a.good_id = b.good_id
        |     where b.good_id is null and a.is_onsell = 'false')
      """.stripMargin)
//        .show()

    /**
      * |count_id|sellCount|    salesAmount|
      * +--------+---------+---------------+
      * |   35644|5534548.0|1.26536729692E9|
      */

//    关联到的下架的
//    sqlContext.sql(
//      """
//        |select
//        |count(good_id) count_id,
//        |sum(sellCount - sell) sell_count,
//        |sum((sellCount - sell) * priceText)salesAmount
//        |from(
//        |     select
//        |     a.good_id,
//        |     add_to_field[size(add_to_field)-1].sellCount sellCount,
//        |     add_to_field[size(add_to_field)-1].priceText priceText,
//        |     b.sellCount sell
//        |     from SourceData a
//        |     join
//        |     lastData b
//        |     on a.good_id = b.good_id)
//      """.stripMargin)
//        .show()

    /**
      * |count_id|sell_count|   salesAmount|
      * +--------+----------+--------------+
      * |   30695|-1028686.0|-8.924795071E7|
      * +--------+----------+--------------+
      */



//    未下架的相乘
//    sqlContext.sql(
//      """
//        |select
//        |count(good_id) count_id,
//        |sum(sellCount)sellCount,
//        |sum(sellCount*priceText) salesAmount
//        |    from(
//        |     select
//        |     good_id,
//        |     add_to_field[size(add_to_field)-1].sellCount sellCount,
//        |     add_to_field[size(add_to_field)-1].priceText priceText
//        |     from SourceData
//        |     where is_onsell = 'true')
//      """.stripMargin)
//        .show()

    /**
      * |count_id|sellCount|         salesAmount|
      * +--------+---------+--------------------+
      * |   88839|2402637.0|2.8853819751999986E8|
      */


      sc.stop()
  }
}
